﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Configuration
Imports System.Web.UI

Public Class Utility
    Public Shared Function GetProdPict1(ByVal p_xc26part As String) As DataTable
        Dim sql As String
        sql = String.Format("SELECT " + _
                            " DISTINCT typ, pic, pic1, pic2" + _
                            " FROM nt" + _
                            " WHERE (xc26aprt LIKE '{0}%')", p_xc26part)
        GetProdPict1 = Utility.query(sql)
    End Function

    Public Shared Function GetProdPict(ByVal p_xc26part As String) As DataTable
        Dim sql As String
        sql = String.Format("SELECT " + _
                            " typ" + _
                            " , IIF(EMPTY(pic),  '沒圖片', '看圖片') AS pic" + _
                            " , IIF(EMPTY(pic1), '沒圖片', '看圖片') AS pic1" + _
                            " , IIF(EMPTY(pic2), '沒圖片', '看圖片') AS pic2" + _
                            " FROM" + _
                            "	(SELECT DISTINCT typ, pic, pic1, pic2" + _
                            "                       FROM          nt" + _
                            "                       WHERE      (xc26aprt LIKE '{0}')" + _
                            "	)AS A" _
                            , p_xc26part)
        Dim dt, newDT As DataTable
        Dim row, newRow As DataRow
        Dim ss As String
        Dim i As Integer
        dt = Utility.query(sql)
        newDT = dt.Clone()
        For Each row In dt.Rows
            newRow = newDT.NewRow()
            For i = 0 To row.ItemArray.Length - 1
                ss = CType(row.ItemArray.GetValue(i), String)
                If (i = 0) Then
                    newRow(i) = row(i)
                Else
                    newRow(i) = row(i).Replace("\", "/").Trim()
                End If
            Next
            newDT.Rows.Add(newRow)
        Next
        Return newDT
    End Function

    Public Shared Function GetCarProdData(ByVal p_car As String, ByVal p_product As String) As DataTable
        Dim sql, var1, var2 As String
        var1 = " pu_car.nam LIKE '{0}%'"
        var2 = " pu_car.pu_nam LIKE '{0}%' "
        If p_car.Length > 0 Then
            If (p_car.Trim().Equals(".") Or p_car.Trim().Equals("..")) Then
                var1 = " 1=1 "
            Else
                var1 = String.Format(var1, p_car.Trim())
            End If
        Else
            var1 = " 1=1 "
        End If
        If p_product.Length > 0 Then
            If (p_product.Trim().Substring(0, 1).Equals("<")) Then
                var2 = " (LEFT(PU_CAR.PU_NAM,1) NOT IN ('<','.') )  "
            Else
                var2 = String.Format(var2, p_product.Trim())
            End If
        Else
            var2 = " 1=1 "
        End If
        sql = String.Format("SELECT " + _
                "	 ALLTRIM(pu.pu_id) + '$ ' + ALLTRIM(pu_car.id) AS PUID,pu_car.p1" + _
                "	,pu_car.pu_nam" + _
                "	,pu.qq" + _
                "	,pu_car.nam" + _
                "	,pu_car.mo" + _
                "	,pu_car.price " + _
                "FROM pu_car,pu  " + _
                "WHERE " + _
                "	pu_car.pu_id=pu.pu_id " + _
                "   AND {0} " + _
                "	AND {1}  " + _
                "ORDER BY 2 ASC", var1, var2)
        GetCarProdData = Utility.query(sql)
        'SELECT * FROM pu WHERE pu_id = '_1I10M27TH'
    End Function

    Public Shared Function GetSeleShopList(ByVal str As String _
                                           , ByVal strCar As String) As DataTable
        Dim sql As String
        sql = String.Format("SELECT " + _
                "	pu.pu_id,pu_car.p1" + _
                "	,pu_car.pu_nam" + _
                "	,pu.qq" + _
                "	,pu_car.nam" + _
                "	,pu_car.mo" + _
                "	,pu_car.price " + _
                "   ,ALLTRIM(pu.pu_id) + '$ ' + ALLTRIM(pu_car.id) AS PUID " + _
                " FROM pu_car,pu  " + _
                " WHERE " + _
                "	pu_car.pu_id=pu.pu_id " + _
                " AND PU.PU_ID IN({0})" + _
                " AND PU_CAR.ID IN({1})" _
                , str, strCar)
        GetSeleShopList = Utility.query(sql)
    End Function

    Public Shared Function GetCarType() As DataTable
        Dim sql As String
        sql = "SELECT DISTINCT NAM FROM QMG4 ORDER BY nam ASC"
        Return Utility.query(sql)
    End Function
    Public Shared Function GetProduct() As DataTable
        Dim sql As String
        sql = "SELECT DISTINCT NAM FROM QMG2 ORDER BY nam ASC"
        Return Utility.query(sql)
    End Function
    '給AutoComplete來使用
    Public Shared Function GetProduct2(ByVal q2 As String, ByVal tbl As String) As DataTable
        Dim sql As String
        sql = String.Format("SELECT ID,NAM FROM {1} WHERE ID LIKE '{0}%' ORDER BY ID ASC" _
                            , q2, tbl)
        GetProduct2 = Utility.query(sql)
    End Function

    Protected Shared Function getConnectionString() As String
        'Provider=vfpoledb;Data Source=C:\MyDbFolder\MyDbContainer.dbc;Collating Sequence=machine;
        getConnectionString = String.Format("Provider=vfpoledb;Data Source={0}\Data\;Collating Sequence=machine;" _
                                            , Utility.getPhysicalPath())
    End Function

    Protected Shared Function query(ByVal p_sql As String) As DataTable
        Dim cn As System.Data.OleDb.OleDbConnection
        Dim cmd As System.Data.OleDb.OleDbCommand
        Dim dr As System.Data.OleDb.OleDbDataReader

        Dim dt As New System.Data.DataTable()
        Try
            cn = New System.Data.OleDb.OleDbConnection
            cn.ConnectionString = getConnectionString()
            cn.Open()

            cmd = cn.CreateCommand()
            cmd.CommandText = p_sql
            dr = cmd.ExecuteReader(Data.CommandBehavior.CloseConnection)
            dt.Load(dr)
        Catch ex As Exception
            HttpContext.Current.Response.Write(ex.Message)
        Finally
            If (cn Is Nothing) Then
            Else
                cn.Close()
            End If

        End Try

        Return dt
    End Function

    Public Shared Function Tif2JPG(ByVal urlPath As String) As Boolean
        Dim ok As Boolean
        ok = True
        Dim tifPath, jpgPath As String
        tifPath = String.Format("{0}{1}", getPhysicalPath(), urlPath)
        jpgPath = String.Format("{0}{1}", getPhysicalPath(), urlPath.ToLower().Replace("tif", "jpg"))
        Try
            If (Not System.IO.File.Exists(jpgPath)) Then
                Dim tif As System.Drawing.Image
                tif = System.Drawing.Image.FromFile(tifPath)
                tif.Save(jpgPath, System.Drawing.Imaging.ImageFormat.Jpeg)
            End If
        Catch ex As Exception
            ok = False
        End Try
        Tif2JPG = ok
    End Function

    Protected Shared Function getPhysicalPath() As String
        getPhysicalPath = HttpContext.Current.Server.MapPath(".")
    End Function

    Public Shared Function ParsePartNo(ByRef p1 As String) As String
        Dim ss As String
        ss = p1
        If (p1.Substring(4, 1).Equals("E")) Then
            ss = p1.Substring(0, 4) + p1.Substring(5, 3)
        Else
            If (p1.Substring(5, 1).Equals("E")) Then
                ss = p1.Substring(1, 4) + p1.Substring(6, 5)
            End If
        End If

        If (p1.Substring(0, 1).Equals("P")) And p1.Substring(1, 1).Equals("Y") Then
            ss = p1.Substring(2, 4) + p1.Substring(6, 6)
        End If

        If p1.Substring(0, 1) = "P" And IsAlpha(p1.Substring(1, 1)) = False Then
            ss = p1.Substring(2, 4) + p1.Substring(6, 6)
        End If

        If (p1.Substring(0, 1).Equals("P")) And (Not p1.Substring(5, 1).Equals("E")) Then
            ss = p1.Substring(1, 4) + p1.Substring(5, 5)
        End If

        ParsePartNo = ss
    End Function

    Public Shared Function IsAlpha(ByVal cExpression As String) As Boolean
        'Check if the first character is a letter
        Return Char.IsLetter(cExpression.Chars(0))
    End Function

    Public Shared Sub LoadJquery(ByVal p_control As Control, ByVal p_type As System.Type)
        ScriptManager.RegisterClientScriptInclude(p_control _
                , p_type, "jquery" _
                , Utility.GetWebConfigAppSettingsValue("jquery"))

        ScriptManager.RegisterClientScriptInclude(p_control _
                , p_type, "jqueryUI" _
                , Utility.GetWebConfigAppSettingsValue("jqueryUI"))
    End Sub

    Public Shared Sub LoadAutoComplete(ByVal p_control As Control, ByVal p_type As System.Type)
        ScriptManager.RegisterClientScriptInclude(p_control _
                , p_type, "autoComplete" _
                , Utility.GetWebConfigAppSettingsValue("autoComplete"))

    End Sub

    Public Shared Function GetWebConfigAppSettingsValue(ByVal p_key As String) As String
        GetWebConfigAppSettingsValue = ConfigurationManager.AppSettings(p_key).ToString()
    End Function

    Public Shared Function GetShopingList(ByVal p_sDate As String _
                                          , ByVal p_eDate As String) As DataTable
        Dim sql, v1 As String

        sql = "SELECT " + _
              "shopCar.contact" + _
              ",shopcar.contact_tel " + _
              ",shopcar.contact_tel2 " + _
              ",shopinglist.pu_id " + _
              ",shopinglist.pu_car_id " + _
              ",shopcar.init_date " + _
              "FROM SHOPCAR LEFT OUTER JOIN shopinglist " + _
              "ON SHOPCAR.SHOPID =  shopinglist.SHOPID " + _
              "WHERE 1=1 {0}"


        If String.Empty.Equals(p_sDate) And String.Empty.Equals(p_eDate) Then
            v1 = "AND empty(SHOPCAR.get_id) " + _
                    "AND (NOT ISNULL(shopinglist.pu_id)) "
        Else
            v1 = String.Format("AND (shopcar.get_date BETWEEN CTOT('{0} T00:00:00Z') AND CTOT('{1} T00:00:00Z')) " _
            , p_sDate, p_eDate)

        End If
        sql = String.Format(sql, v1)
        'sql = "SELECT " + _
        '      "shopCar.contact" + _
        '      ",shopcar.contact_tel " + _
        '      ",shopcar.contact_tel2 " + _
        '      ",shopinglist.pu_id " + _
        '      ",shopinglist.pu_car_id " + _
        '      "FROM SHOPCAR LEFT OUTER JOIN shopinglist" + _
        '      "ON SHOPCAR.SHOPID =  shopinglist.SHOPID " + _
        '      "WHERE empty(SHOPCAR.get_id)" + _
        '      "AND (NOT ISNULL(shopinglist.pu_id) )"
        '"        GetShopingList = String.Empty"
        GetShopingList = query(sql)
    End Function

    Public Shared Sub Updshopcar(ByVal p_id As String)
        Dim sql As String
        sql = "UPDATE shopcar SET get_id=? ,get_date=datetime() WHERE empty(get_id)"
        Dim cmd As System.Data.OleDb.OleDbCommand
        Using cn As New OleDb.OleDbConnection(getConnectionString())
            cn.Open()
            cmd = cn.CreateCommand
            cmd.CommandText = sql
            cmd.Parameters.Add(getParameter(cmd.CreateParameter, DbType.String, p_id))
            cmd.ExecuteNonQuery()
        End Using
    End Sub

    Public Shared Function ShopingClose(ByVal p_contact As String, _
                ByVal p_contact_tel As String, ByVal p_contact_tel2 As String, _
                ByVal p_list As System.Collections.Generic.List(Of String)) As Boolean

        Dim sql, shopID As String
        shopID = System.Guid.NewGuid.ToString
        sql = "INSERT INTO shopcar(contact,contact_tel,contact_tel2,get_id,shopid) " + _
               "VALUES(?,?,?,'',?)"
        Dim cmd As System.Data.OleDb.OleDbCommand
        Using cn As New OleDb.OleDbConnection(getConnectionString())
            Try
                cn.Open()
                'tran = cn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted)

                cmd = cn.CreateCommand
                cmd.CommandText = sql
                'cmd.Transaction = tran

                cmd.Parameters.Add(getParameter(cmd.CreateParameter, DbType.String, p_contact))

                cmd.Parameters.Add(getParameter(cmd.CreateParameter, DbType.String, p_contact_tel))

                cmd.Parameters.Add(getParameter(cmd.CreateParameter, DbType.String, p_contact_tel))

                cmd.Parameters.Add(getParameter(cmd.CreateParameter, DbType.String, shopID))

                cmd.ExecuteNonQuery()
                ShopingClose = True

                If (ShopingClose = True) Then
                    '--新增for detail
                    cmd = cn.CreateCommand
                    Dim str As String
                    Dim strSplit As Char() = New [Char]() {"$"}
                    Dim idCollect() As String
                    sql = String.Format("INSERT INTO shopinglist(shopid,pu_id,pu_car_id) VALUES('{0}',?,?)", shopID)
                    For Each str In p_list
                        idCollect = str.Split(strSplit)
                        cmd.CommandText = sql

                        cmd.Parameters.Add(getParameter(cmd.CreateParameter, DbType.String, idCollect(0).Trim))

                        cmd.Parameters.Add(getParameter(cmd.CreateParameter, DbType.String, idCollect(1).Trim))

                        cmd.ExecuteNonQuery()
                    Next
                End If
                'tran.Commit()
            Catch ex As Exception
                ShopingClose = False
                '--新增失敗 要刪除主檔
                sql = String.Format("delete from shopcar where shopid='{0}'", shopID)
                cmd = cn.CreateCommand
                cmd.CommandText = sql
                cmd.ExecuteNonQuery()
                'tran.Rollback()
            Finally

            End Try

        End Using



    End Function

    Protected Shared Function getParameter(ByVal p_para As OleDb.OleDbParameter, _
            ByVal p_dbType As DbType, ByVal p_val As Object) As OleDb.OleDbParameter
        p_para.Value = p_val
        p_para.DbType = p_dbType
        getParameter = p_para
    End Function

    Public Shared Function GetDateTimePW(ByVal p_pw As String) As Boolean
        Dim y1, m1, d1, t1, pw As String
        y1 = Date.Today.Year.ToString()
        m1 = Date.Today.Month.ToString()
        m1 = IIf(m1.Length < 2, String.Format("0{0}", m1), m1)
        d1 = Date.Today.Day.ToString()
        d1 = IIf(d1.Length < 2, String.Format("0{0}", d1), d1)
        t1 = Date.Now.Hour.ToString()
        t1 = IIf(t1.Length < 2, String.Format("0{0}00", t1) _
            , String.Format("{0}00", t1))
        pw = String.Format("{0}{1}{2}{3}", y1, m1, d1, t1)
        GetDateTimePW = IIf(pw.Equals(p_pw), True, False)
    End Function
End Class
